module.exports = {
  // 获取所有分类
  GET_CATEGORIES: `
    SELECT 
      c.*,
      COUNT(ccr.course_id) as course_count
    FROM categories c
    LEFT JOIN category_course_relations ccr ON c.id = ccr.category_id
    WHERE c.status = 'active'
    GROUP BY c.id
    ORDER BY c.sort_order ASC
  `,

  // 根据分类ID获取课程列表
  GET_COURSES_BY_CATEGORY: `
    SELECT 
      c.*,
      u.real_name as teacher_name,
      COUNT(DISTINCT ce.student_id) as enrolled_students,
      GROUP_CONCAT(DISTINCT ct.name) as tags
    FROM courses c
    LEFT JOIN users u ON c.teacher_id = u.id
    LEFT JOIN course_enrollments ce ON c.id = ce.course_id
    LEFT JOIN course_tag_relations ctr ON c.id = ctr.course_id
    LEFT JOIN course_tags ct ON ctr.tag_id = ct.id
    JOIN category_course_relations ccr ON c.id = ccr.course_id
    WHERE c.status = 'published'
    AND ccr.category_id = ?
    GROUP BY c.id
    ORDER BY c.created_at DESC
  `
}; 